﻿/*	WebClip数据库封装
*	modify: tantianzuo@gmail.com
* date:2010/01/16
*
*	author: haozes@gmail.com
*	date:2010/01/13
*/
var Class = {
  create: function() {
    return function() {
      this.initialize.apply(this, arguments);
    }
  }
}
var DB_article=Class.create();
DB_article.prototype=
{
	initialize: function() {
		this.dbName='DB_WebClipB';
		this.db=null;
	},
	init:function(){
		if (window.openDatabase){
		  console.log("create table");
			this.db = window.openDatabase(this.dbName, "1.0",this.dbName, 1024*1024);
			if(!db)
				console.log(result);
			this.db.transaction(function(query) {
					query.executeSql('CREATE TABLE IF NOT EXISTS [T_sync_article] ('
									  +'[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,' 
                                      +'[GID] INTEGER,' 
                                      +'[GVER] INTEGER,' 
                                      +'[DEL] INTEGER,' 
                                      +'[MOD] INTEGER,' 
									  +'[title] NVARCHAR(30) NOT NULL,' 
									  +'[body] NVARCHAR(600),' 
									  +'[pageUrl] NVARCHAR(200),' 
									  +'[addTime] DATETIME)' ,
									[]
  					,function(transaction, result) {
  						console.log(result);
  					}
  					,function(transaction, error) {
  						console.log(error);
  					}
					);
          query.executeSql('CREATE TABLE IF NOT EXISTS [T_tags] ('
									  +'[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,' 
									  +'[name] NVARCHAR(30) NOT NULL,' 
									  +'[description] NVARCHAR(600))',
									[]
  					,function(transaction, result) {
  						console.log(result);
  					}
  					,function(transaction, error) {
  						console.log(error);
  					}
					);
          query.executeSql('CREATE TABLE IF NOT EXISTS [T_tag_article] ('
									  +'[id_tag] INTEGER NOT NULL PRIMARY KEY,' 
									  +'[id_article] INTEGER NOT NULL )',
									[]
  					,function(transaction, result) {
  						console.log(result);
  					}
  					,function(transaction, error) {
  						console.log(error);
  					}
					);
									
					query.executeSql('SELECT COUNT(1) as num FROM T_tags'
    			,[]
    			,function(transaction, result){
    				var count=result.rows.item(0)['num'];
    				console.log("tag count=" + count);
    				if(count<=0)
    				{
    					console.log("创建预定义tags");
    					query.executeSql('INSERT INTO T_tags(name, description) VALUES (?,?);'
      					,["未分类","还没整理的东西放这里"]
      					,function(transaction, result) {
      						console.log(result);
      					}
      					,function(transaction, error) {
      						console.log(error);
      					}
    					);
    					console.log("创建预定义tags ok");
    				}
    			}
    		  );
			});
		}	
	},
	Exec:function(sql,para,transHandler,errHandler){
			this.db.transaction(function(query) {
				query.executeSql(sql,
								para,
								function(transaction, result) {
									console.log(result);
									if(transHandler)
										transHandler(result);
								},
								function(transaction, error) {
									console.log(sql);
									console.log(error);
									if(errHandler)
										errHandler(error);
								});
			});
	},
	NewWord:function(title,sen,url){
		console.log("add title" + (new Date()).getTime());
		this.Exec('INSERT INTO T_sync_article(GID,GVER,DEL,MOD,title, body,pageUrl,addTime) VALUES (?,?,?,?,?,?,?,?)'
				,[0,0,false,false,title, sen,url,(new Date()).getTime()]);
	},
	NewTag:function(name,description){
		console.log("NewTag");
		this.Exec('INSERT INTO T_tags(name, description) VALUES (?,?)'
				,[name,description]);
	},
	Update: function(title, sen, url) {
		console.log("Update");
		this.Exec('UPDATE  T_sync_article SET body=?,pageUrl=?,addTime=? WHERE title=?'
				,[sen,url,(new Date()).getTime(),title]);
	},
	GetWord:function(title,fun){
			var w=null;
			this.Exec('SELECT * FROM T_sync_article WHERE title=?'
				,[title]
				,function(result){
									if(result.rows.length>0){
										var row=result.rows.item(0);
										console.log(row);
										w = {
												id: row['id'],
												title: row['title'],
												body: row['body'],
												pageUrl:row['pageUrl'],
												addTime:row['addTime']
											};
									}
									return fun(w);
								}
			);
	},
	DeleteArticle:function(id){
				this.Exec('DELETE FROM T_sync_article WHERE id=?'
				,[id]);
	},
	GetRows:function(pageIndex,pageSize,order,fun){
		if(!order)
			order='order by [addTime] desc';
		var offset = pageSize * pageIndex;
		var w=null;
        var sql = 'SELECT * FROM T_sync_article ' + order + ' limit '+offset+','+pageSize+';';
        console.log(sql);
		this.Exec(sql
				,[]
				,function(result) {
						for(var i=0;i<result.rows.length;i++){
                        //for(var i=result.rows.length-1;i>=0;i--){
							var row = result.rows.item(i)
							w = {
								id: row['id'],
								title: row['title'],
								body: row['body'],
								pageUrl:row['pageUrl'],
								addTime:row['addTime']
							}
							//console.log(w);
							fun(w);
						}
				}
		);
	},
	GetAll:function(fun)
	{
		this.Exec('SELECT * FROM T_sync_article order by addtime desc'
				,[]
				,function(result) {
						fun(result);
				}
		)
	},
	GetTotalCount:function(fun){
		this.Exec('SELECT COUNT(1) as num FROM T_sync_article'
			,[]
			,function(result){
				console.log(result.rows.length);
				var count=result.rows.item(0)['num'];
				fun(count);
			}
		);
	},
	GetTags:function(fun){
		//console.log("GetTags");
		this.Exec('SELECT * FROM T_tags;'
				,[]
				,function(result) {
						for(var i=0;i<result.rows.length;i++){
							var row = result.rows.item(i)
							w = {
								id: row['id'],
								name: row['name'],
								description: row['description']
							}
							console.log("row_id:" + row['id']);
							fun(w);
						}
				}
		);
	},
	GetTagsCount:function(fun){
		this.Exec('SELECT COUNT(1) as num FROM T_tags'
			,[]
			,function(result){
				console.log(result.rows.length);
				var count=result.rows.item(0)['num'];
				fun(count);
			}
		);
	},
	AddRow:function(title,body,pageUrl,addTime)
	{
		this.Exec('INSERT INTO T_article(title, body,pageUrl,addTime) VALUES (?,?,?,?)'
				,[title,body,pageUrl,addTime]);
	},
    ///////////////////////////////////////////////
	GetRowToUpdate:function(fun){
		//console.log("GetTags");
		this.Exec('SELECT * FROM T_sync_article WHERE GID=0 LIMIT 0,1'
				,[]
				,function(result) {
						if(result.rows.length<=0)
                        {
                            fun(null);
                            return;
                        }
                        for(var i=0;i<result.rows.length;i++){
							var row = result.rows.item(i)
							w = {
                                    id: row['id'],
                                    gid: row['GID'],
                                    gver: row['GVER'],
                                    del: row['DEL'],
                                    mod: row['MOD'],
                                    title: row['title'],
                                    body: row['body'],
                                    pageUrl:row['pageUrl'],
                                    addTime:row['addTime']
							}
							fun(w);
						}
				}
		);
	},
	SetRowSyn: function(id,gid,gver,del) {
		console.log("SetRowSyn:" + id);
        this.db.transaction(function(query) {
				query.executeSql('UPDATE  T_sync_article SET GID=?,GVER=?,DEL=?,MOD=? WHERE id=?',
								[gid,gver,del,false,id]);
			});
	},
	GetRowByGID: function(gid,fun) {
        this.Exec('SELECT * FROM T_sync_article WHERE GID=?'
				,[gid]
				,function(result) {
						if(result.rows.length<=0)
                        {
                            fun(null);
                            return;
                        }
                        for(var i=0;i<result.rows.length;i++){
							var row = result.rows.item(i)
							w = {
                                    id: row['id'],
                                    gid: row['GID'],
                                    gver: row['GVER'],
                                    MOD: row['MOD'],
                                    title: row['title'],
                                    body: row['body'],
                                    pageUrl:row['pageUrl'],
                                    addTime:row['addTime']
							}
							fun(w);
						}
				}
		);
	},
	AddArticle:function(gid,gver,del,title,sen,url,addTime){
        addTime = addTime || new Date();
		this.Exec('INSERT INTO T_sync_article(GID,GVER,DEL,MOD,title, body,pageUrl,addTime) VALUES (?,?,?,?,?,?,?,?)'
				,[gid,gver,del,false,title, sen,url,addTime.getTime()]);
	},
	UpdateArticle:function(gid,gver,del,title,sen,url,addTime){
		addTime = addTime || new Date();
        this.Exec('UPDATE T_sync_article set GVER=?,DEL=?,MOD=?,title=?,body=?,pageUrl=?,addTime=? WHERE GID=?'
				,[gver,del,false,title, sen,url,addTime.getTime(),gid]);
	},
    CheckUpdate:function(gid,gver,del,title,body,pageUrl,addTime){
        db.GetRowByGID(gid,function (rs){
            /*id: row['id'],
            gid: row['GID'],
            gver: row['GVER'],
            del: row['DEL'],
            mod: row['MOD'],
            title: row['title'],
            body: row['body'],
            pageUrl:row['pageUrl'],
            addTime:row['addTime']*/
            if(rs==null)
            {
                //rs.del?
                db.AddArticle(gid,gver,del,title,body,pageUrl,addTime);
                return;
            }
            db.UpdateArticle(gid,gver,del,title,body,pageUrl,addTime);
        });
    }
  
};